package mes.kanban.respository;

import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import mes.kanban.entity.MesJob;

/**
 * 用户作业
 * @author zongrui.liu
 *
 */
@Repository
public interface UserJobRespository extends BaseJpaRespository<MesJob, String>{
	
	/**
	 * 获取作业通过用户角色ID
	 * @param id
	 * @return
	 */
	@Query(nativeQuery=true,value="select j.* from mes_job j LEFT JOIN mes_role_job rj ON j.id = rj.job_id LEFT JOIN mes_role r ON r.id = rj.role_id where rj.role_id = ? order by j.job_content")
	List<MesJob> getJobByRoleId(String id);

	/**
	 * 通过用户ID，作业编码查询用户对应的作业列表
	 * @param userId  用户Id 
	 * @param jobCode 作业编码
	 * @return
	 */
	@Query(nativeQuery=true,value="select j.* from mes_user u LEFT JOIN mes_user_role ur ON u.id=ur.user_id LEFT JOIN mes_role r on ur.role_id = r.id LEFT JOIN mes_role_job rj ON r.id = rj.role_id "
			+ " LEFT JOIN mes_job j ON rj.job_id = j.id where u.id =:userId and j.job_code like CONCAT(:jobCode,'%') ")
	List<MesJob> findJobByUserId(@Param("userId")String userId,@Param("jobCode")String jobCode);

	/**
	 * 通过用户ID，作业编码查询用户对应的作业ID列表
	 * @param userId  用户Id 
	 * @param jobCode 作业编码
	 * @return
	 */
	@Query(nativeQuery=true,value="select j.id from mes_user u LEFT JOIN mes_user_role ur ON u.id=ur.user_id LEFT JOIN mes_role r on ur.role_id = r.id LEFT JOIN mes_role_job rj ON r.id = rj.role_id "
			+ " LEFT JOIN mes_job j ON rj.job_id = j.id where u.id =:userId and j.job_code like CONCAT(:jobCode,'%') ")
	List<String> findJobIdByUserId(@Param("userId")String userId, @Param("jobCode")String jobCode);

	/**
	 * 通过用户ID，作业编码查询用户对应的作业内容列表
	 * @param userId  用户Id 
	 * @param jobCode 作业编码
	 * @return
	 */
	@Query(nativeQuery=true,value="select j.job_content from mes_user u LEFT JOIN mes_user_role ur ON u.id=ur.user_id LEFT JOIN mes_role r on ur.role_id = r.id LEFT JOIN mes_role_job rj ON r.id = rj.role_id "
			+ " LEFT JOIN mes_job j ON rj.job_id = j.id where u.id =:userId and j.job_code like CONCAT(:jobCode,'%') ")
	List<String> findJobContentByUserId(@Param("userId")String userId,@Param("jobCode")String jobCode);


}
